const { pool, query: dbQuery } = require('../config/database');
const { logger } = require('../utils/logger');

/**
 * 菜单分类模型类
 * 处理菜单分类相关的数据库操作
 */
class MenuCategory {
  constructor(data = {}) {
    this.id = data.id;
    this.name = data.name;
    this.name_en = data.name_en;
    this.description = data.description;
    this.description_en = data.description_en;
    this.icon = data.icon;
    this.color = data.color;
    this.is_active = data.is_active !== undefined ? data.is_active : true;
    this.sort_order = data.sort_order || 0;
    this.created_at = data.created_at;
    this.updated_at = data.updated_at;
  }

  /**
   * 创建新菜单分类
   */
  static async create(categoryData) {
    try {
      const {
        name, 
        name_en = null, 
        description = null, 
        description_en = null,
        icon = null, 
        color = '#FF6B35', 
        is_active = true, 
        sort_order = 0
      } = categoryData;

      const result = await dbQuery(
        `INSERT INTO menu_categories (
          name, name_en, description, description_en,
          icon, color, is_active, sort_order
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
        [name, name_en, description, description_en, icon, color, is_active, sort_order]
      );

      return await MenuCategory.findById(result.insertId);
    } catch (error) {
      logger.error('创建菜单分类失败:', error);
      throw error;
    }
  }

  /**
   * 根据ID查找菜单分类
   */
  static async findById(id) {
    try {
      const rows = await dbQuery(
        'SELECT * FROM menu_categories WHERE id = ? AND deleted_at IS NULL',
        [id]
      );

      if (rows.length === 0) {
        return null;
      }

      return new MenuCategory(rows[0]);
    } catch (error) {
      logger.error('查找菜单分类失败:', error);
      throw error;
    }
  }

  /**
   * 获取所有菜单分类
   */
  static async findAll(options = {}) {
    try {
      const {
        is_active,
        sort_by = 'sort_order',
        sort_order = 'ASC'
      } = options;

      let query = 'SELECT * FROM menu_categories WHERE deleted_at IS NULL';
      const params = [];

      if (is_active !== undefined) {
        query += ' AND is_active = ?';
        params.push(is_active);
      }

      // 添加排序
      const validSortFields = ['id', 'name', 'sort_order', 'created_at', 'updated_at'];
      const sortField = validSortFields.includes(sort_by) ? sort_by : 'sort_order';
      const sortDirection = sort_order.toUpperCase() === 'DESC' ? 'DESC' : 'ASC';
      query += ` ORDER BY ${sortField} ${sortDirection}`;

      const rows = await dbQuery(query, params);
      return rows.map(row => new MenuCategory(row));
    } catch (error) {
      logger.error('获取菜单分类列表失败:', error);
      throw error;
    }
  }

  /**
   * 获取活跃的菜单分类
   */
  static async getActive() {
    return await MenuCategory.findAll({ is_active: true });
  }

  /**
   * 更新菜单分类
   */
  static async update(id, updateData) {
    try {
      const fields = [];
      const values = [];

      const allowedFields = [
        'name', 'name_en', 'description', 'description_en',
        'icon', 'color', 'is_active', 'sort_order'
      ];

      for (const field of allowedFields) {
        if (updateData.hasOwnProperty(field)) {
          fields.push(`${field} = ?`);
          values.push(updateData[field]);
        }
      }

      if (fields.length === 0) {
        throw new Error('没有提供要更新的字段');
      }

      fields.push('updated_at = NOW()');
      values.push(id);

      const query = `UPDATE menu_categories SET ${fields.join(', ')} WHERE id = ? AND deleted_at IS NULL`;
      const result = await dbQuery(query, values);

      if (result.affectedRows === 0) {
        return null;
      }

      return await MenuCategory.findById(id);
    } catch (error) {
      logger.error('更新菜单分类失败:', error);
      throw error;
    }
  }

  /**
   * 软删除菜单分类
   */
  static async delete(id) {
    try {
      // 检查是否有菜单项使用此分类
      const menuItems = await dbQuery(
        'SELECT COUNT(*) as count FROM menu_items WHERE category_id = ? AND deleted_at IS NULL',
        [id]
      );

      if (menuItems[0].count > 0) {
        throw new Error('无法删除分类，该分类下还有菜单项');
      }

      const result = await dbQuery(
        'UPDATE menu_categories SET deleted_at = NOW() WHERE id = ? AND deleted_at IS NULL',
        [id]
      );

      return result.affectedRows > 0;
    } catch (error) {
      logger.error('删除菜单分类失败:', error);
      throw error;
    }
  }

  /**
   * 批量更新排序
   */
  static async updateSortOrder(categories) {
    try {
      const promises = categories.map(category => 
        dbQuery(
          'UPDATE menu_categories SET sort_order = ?, updated_at = NOW() WHERE id = ? AND deleted_at IS NULL',
          [category.sort_order, category.id]
        )
      );

      await Promise.all(promises);
      return true;
    } catch (error) {
      logger.error('批量更新分类排序失败:', error);
      throw error;
    }
  }

  /**
   * 获取分类及其菜单项数量
   */
  static async getCategoriesWithItemCount() {
    try {
      const rows = await dbQuery(`
        SELECT 
          c.*,
          COUNT(m.id) as item_count,
          COUNT(CASE WHEN m.is_available = 1 THEN 1 END) as available_count
        FROM menu_categories c
        LEFT JOIN menu_items m ON c.id = m.category_id AND m.deleted_at IS NULL
        WHERE c.deleted_at IS NULL
        GROUP BY c.id
        ORDER BY c.sort_order ASC
      `);

      return rows.map(row => ({
        ...new MenuCategory(row),
        item_count: row.item_count,
        available_count: row.available_count
      }));
    } catch (error) {
      logger.error('获取分类统计失败:', error);
      throw error;
    }
  }
}

module.exports = MenuCategory;